﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;

namespace QLNhaThuoc.DAL
{
    class DataProvider
    {
        protected static string _connectionString;

        public static string ConnectionString
        {
            get { return DataProvider._connectionString; }
            set { DataProvider._connectionString = value; }
        }

        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }

        protected OleDbConnection connection;

        protected OleDbDataAdapter adapter;

        protected DataTable myTable;

        protected OleDbCommand command;

        private string _tableName;

        public void connect()
        {
            connection = new OleDbConnection(_connectionString);
            connection.Open();
        }

        public void disconnect()
        {
            connection.Close();
        }

        public OleDbDataReader executeQuery(string sqlString)
        {
            command = new OleDbCommand(sqlString, connection);
            return command.ExecuteReader();
        }

        public void fillMyTable(string tableName)
        {
            _tableName = tableName;
            myTable = new DataTable();
            string sqlString = "select * from " + tableName;
            adapter = new OleDbDataAdapter(sqlString, connection);            
            adapter.FillSchema(myTable, SchemaType.Mapped);
            adapter.Fill(myTable);
            OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);
        }

        public void updateMyTable()
        {
            try
            {
                adapter.Update(myTable);
                myTable.AcceptChanges();
            }
            catch(Exception exc)
            {
                string ex = exc.Message;
                myTable.RejectChanges();
            }
        }

        public DataTable getCustomDataTable(string sqlString)
        {
            DataTable table = new DataTable();
            OleDbDataAdapter tmpAdapter = new OleDbDataAdapter(sqlString, connection);
            tmpAdapter.Fill(table);
            return table;
        }

        public void executeNonQuery(string sqlString)
        {
            command = new OleDbCommand(sqlString, connection);
            command.ExecuteNonQuery();
        }

        public object executeScalar(string sqlString)
        {
            command = new OleDbCommand(sqlString, connection);
            return command.ExecuteScalar();
        }

        public ArrayList DongHopDuLieuTuDataTable()
        {
            int n = myTable.Rows.Count;

            ArrayList list = new ArrayList();

            for (int i = 0; i < n; i++)
            {
                object abstractDTO = ConvertOneRowIntoDTO(myTable, i);
                list.Add(abstractDTO);
            }

            return list;
        }

        public ArrayList DongHopDuLieuTuCustomDatatable(string sql)
        {
            DataTable dt = getCustomDataTable(sql);

            int n = dt.Rows.Count;
            ArrayList list = new ArrayList();

            for (int i = 0; i < n; i++)
            {
                object NewAbstractDTO = ConvertOneRowIntoDTO(dt, i);
                list.Add(NewAbstractDTO);
            }

            return list;
        }

        public virtual object ConvertOneRowIntoDTO(DataTable table , int i)
        {
            return null;
        }

        public string[] GetAllColumns()
        {
            string sqlString =
                "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_Name='" + TableName + "'order by ORDINAL_POSITION";
            DataTable list = getCustomDataTable(sqlString);
            int n = list.Rows.Count;
            string[] result = new string[n];

            for (int i = 0; i < n; i++)
            {
                result[i] = list.Rows[i]["COLUMN_NAME"].ToString();
            }

            return result;
        }
    }
}
